Synopsis: Keyless Entry

The testing lab manager burst into my cubicle, “Bill, it looks like two managers have reserved the same server in our lab for the same days — how could this happen? Can you take a look into this and get it fixed? They’re screaming at me that they both need the equipment and that I’m holding up their project schedule.”

I designed an equipment-tracking application some years ago using MySQL. The default storage engine for MySQL was MyISAM, which doesn’t support foreign key constraints. The database had many logical relationships but could not enforce referential integrity.

As the project evolved and the application manipulated data in new ways, we developed a problem: when referential integrity wasn’t satisfied, discrepancies showed up in reports, subtotals didn’t add up, and the schedules became double-booked.

The project manager asked me to write quality control scripts that we could run periodically to let us know when discrepancies occurred. These scripts examined the state of the database, found mistakes such as orphaned rows in child tables, and sent an email to report them.

Every table relationship had to be checked by these scripts. As the volume of data grew larger and the number of tables increased, the number of quality control queries also grew, and the scripts took longer to run. The email reports became longer too. Sounds familiar?

The script solution worked, of course, but it was a costly reinvention of the wheel. What I needed was a way to make the application fail early whenever a user submitted invalid data. And here is where foreign key constraints come into play!

Objective: Simplify database architecture#

Relational database design is almost as much about relationships between tables as it is about the individual tables themselves. Referential integrity is an important part of proper database design and operation. Whenever we declare a foreign key constraint for a column or set of columns, the values in these columns must exist in the primary key or unique key columns of the parent table. This seems simple enough.

However, some software developers recommend avoiding referential integrity constraints. The reasons we hear about ignoring foreign keys include the following:

• Our data updates can conflict with the constraints.
• We’re using a database design that’s so flexible it can’t support referential integrity constraints.
• We believe that the index the database creates for the foreign key will impact performance.
• We use a database brand that doesn’t support foreign keys.
• We have to look up the syntax for declaring foreign keys.

Legitimate uses of the antipattern#

Sometimes we’re forced to use a database brand that doesn’t support foreign key constraints (for example, MySQL’s MyISAM storage engine or SQLite prior to version 3.6.19). If that’s the case, then we have to find a way to compensate, like the quality control scripts described in this chapter’s story.

There are also some ultra-flexible database designs where foreign keys can’t model the relationships. It should be a strong clue that we’re using another SQL antipattern if we can’t use traditional referential integrity constraints. More details of this are included in the Entity-Attribute-Value and Polymorphic Associations chapters of this course.